from fastapi import APIRouter, Depends
from sqlalchemy.orm import Session
from typing import List
from ..database import get_db
from . import schemas
from . import crud

router = APIRouter(prefix="/utils", tags=["utils"])

@router.get("/students/over-30", response_model=List[schemas.Student], summary="查询30岁以上学生")
def get_students_over_30(db: Session = Depends(get_db)):
    """查询所有超过30岁的学员的信息"""
    return crud.get_students_over_30(db)

@router.get("/students/gender-stats", response_model=List[schemas.ClassGenderStats], summary="统计班级性别分布")
def get_class_gender_stats(db: Session = Depends(get_db)):
    """统计每个班级男生女生的人数"""
    return crud.get_class_gender_stats(db)

@router.get("/scores/high-score-students", response_model=List[schemas.HighScoreStudent], summary="查询高分学生")
def get_high_score_students(db: Session = Depends(get_db)):
    """查询每次考试成绩都在80分以上的学生的编号，姓名和成绩"""
    return crud.get_high_score_students(db)

@router.get("/scores/frequent-fail-students", response_model=List[schemas.FrequentFailStudent], summary="查询多次不及格学生")
def get_frequent_fail_students(db: Session = Depends(get_db)):
    """查询有两次以上不及格的学生的姓名，班级和不及格成绩"""
    return crud.get_frequent_fail_students(db)

@router.get("/scores/class-average-scores", response_model=List[schemas.ClassExamAverageScore], summary="统计班级平均分")
def get_class_average_scores(db: Session = Depends(get_db)):
    """统计每次考试每个班级的平均分，按照从高到低排序"""
    return crud.get_class_average_scores(db)

@router.get("/employment/top-salary-students", response_model=List[schemas.TopSalaryStudent], summary="查询薪资最高学生")
def get_top_salary_students(db: Session = Depends(get_db)):
    """统计就业薪资最高的前五名学生的姓名，班级和就业时间，就业公司"""
    return crud.get_top_salary_students(db)